sql = {}
# 监控事例的等待
sql["event_wait"] = """
SELECT event,sum(decode(wait_Time,0,0,1)) "Prev",
              sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" 
FROM v$session_Wait 
GROUP BY event ORDER BY 4
"""
# 回滚段的争用情况
sql["roll_use"] = """
SELECT name, waits, gets, waits / gets "Ratio"
FROM v$rollstat a, v$rollname b
WHERE a.usn = b.usn
"""
# 查看回滚段名称及大小
sql["roll_size"] = """
SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent,
    (next_extent / 1024) nextextent, max_extents, v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name
"""
# 查看控制文件
sql["controlfile"] = """SELECT name FROM v$controlfile"""
# 查看日志文件
sql["logfile"] = """SELECT member FROM v$logfile"""
# 数据表占用空间大小情况
sql["table_bytes"] = """
SELECT segment_name, tablespace_name, bytes, blocks
FROM user_segments
WHERE segment_type = 'TABLE'
ORDER BY bytes DESC, blocks DESC
"""
# 查看表空间碎片大小
sql["table_blocks_size"] = """
SELECT tablespace_name,
    round(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))),2) FSFI
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY 1
"""
# 查看表空间占用磁盘情况
sql["tablespace_file_size"] = """
SELECT b.file_id, b.tablespace_name, b.bytes,
    (b.bytes-sum(nvl(a.bytes,0))) used,
    sum(nvl(a.bytes,0)) free,
    sum(nvl(a.bytes,0))/(b.bytes)*100 free_precent
FROM dba_free_space a,dba_data_files b
WHERE a.file_id=b.file_id
GROUP BY b.tablespace_name,b.file_id,b.bytes
ORDER BY b.file_id
"""
# 查看表的大小，倒序排列
sql["table_size"] = """
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME ORDER BY MBYTESE DESC
"""
# 查看表空间物理文件的名称及大小
sql["tablespace_data_file_size"] = """
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files ORDER BY tablespace_name
"""
# 查看Oracle 表空间使用率
sql["tablespace_use_precent"] = """
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)",
    SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
    ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
    FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM (
    SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS
    FROM DBA_DATA_FILES
    GROUP BY TABLESPACE_NAME) D,
    ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
    FROM DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME) F
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", USED_SPACE || 'M' "USED_SPACE(M)",
    ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
    NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM (
    SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS
    FROM DBA_TEMP_FILES
    GROUP BY TABLESPACE_NAME) D,
    ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
    ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
    FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1
"""
# 查看Temp 表空间实际使用磁盘大小
sql["temp_tablespace_disk_size"] = """
Select f.tablespace_name, d.file_name "Tempfile name",
    round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
    round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB",
    round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",
    round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) / round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100, 2) as "Used_Rate(%)"
FROM SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
WHERE f.tablespace_name(+) = d.tablespace_name AND f.file_id(+) = d.file_id AND p.file_id(+) = d.file_id
"""
# 查看session使用回滚段
sql["session_roll"] = """
SELECT r.name roll_name, s.sid, s.serial#, s.username username, t.status,
    t.cr_get, t.phy_io, t.used_ublk, t.noundo, substr(s.program, 1, 78) proc
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr AND t.xidusn = r.usn ORDER BY t.cr_get,t.phy_io
"""
# 查看当前临时表空间使用大小与正在占用临时表空间的sql语句
sql["temp_tablespace_use_size"] = """
SELECT sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
FROM v$sort_usage sort, v$session sess, v$sql sql
WHERE sort.SESSION_ADDR = sess.SADDR AND sql.ADDRESS = sess.SQL_ADDRESS
ORDER BY blocks DESC
"""
# Temp表空间上进程的查询
sql["temp_proc"] = """
SELECT a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text
FROM v$sort_usage a,v$session b,v$sqltext c
WHERE a.session_addr = b.saddr AND b.sql_address = c.address
ORDER BY a.tablespace,b.sid,b.serial#,c.address, c.piece
"""
# 查看SGA区剩余可用内存
sql["sga_free_memory"] = """
SELECT name,sgasize/1024/1024 "Allocated(M)", bytes/1024 "** space(K)",round(bytes/sgasize*100, 2) "** space precent(%)"
FROM (
    SELECT sum(bytes) sgasize
    FROM sys.v_$sgastat) s,
    sys.v_$sgastat f
WHERE f.name = 'free memory'
"""
# 监控表空间I/O比例
sql["tablespace_io_precent"] = """
SELECT df.tablespace_name name,df.file_name "file",f.phyrds pyr,
    f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
FROM v$filestat f, dba_data_files df
WHERE f.file# = df.file_id
ORDER BY df.tablespace_name
"""
# 监控SGA命中率
sql["sga_shooting"] = """
SELECT a.value + b.value "logical_reads", c.value "phys_reads",
    round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.statistic# = 38 AND b.statistic# = 39 AND c.statistic# = 40
"""
# 监控 SGA 中字典缓冲区的命中率
sql["sga_dict_buffer_shooting"] = """
SELECT parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
    (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
FROM v$rowcache
WHERE gets+getmisses <>0
GROUP BY parameter, gets, getmisses
"""
# 监控 SGA **享缓存区的命中率，应该小于1%
sql["sga_buffer_shooting"] = """
SELECT sum(pins) "Total Pins", sum(reloads) "Total Reloads",
    sum(reloads)/sum(pins) *100 libcache
FROM v$librarycache
"""
# 监控 SGA 中重做日志缓存区的命中率，应该小于1%
sql["sga_redolog_buffer_shooting"] = """
SELECT name, gets, misses, immediate_gets, immediate_misses,
    Decode(gets,0,0,misses/gets*100) ratio1,
    Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy')
"""
# 监控内存和硬盘的排序比率，最好使它小于 .10
sql["memory_disk_sort"] = """
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)')
"""
# 监控字典缓冲区
sql["dict_buffer"] = """
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
"""
# 非系统用户建在SYSTEM表空间中的表
sql["user_createtable_in_system_tablespace"] = """
SELECT owner,table_name
FROM DBA_TABLES
WHERE tablespace_name in('SYSTEM','USER_DATA')
    AND owner NOT IN('SYSTEM','SYS','OUTLN', 'ORDSYS','MDSYS','SCOTT', 'HOSTEAC')
"""
# 查询使用CPU多的用户session
sql["Cpu-heavy_user_sessions"] = """
SELECT a.sid,spid,status,substr(a.program,1,40) prog,
    a.terminal,osuser,value/60/100 value
FROM v$session a,v$process b,v$sesstat c
WHERE c.statistic#=12 AND c.sid=a.sid AND a.paddr=b.addr
ORDER BY value DESC
"""
# 查看数据库的版本
sql["database_version"] = """
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle'
"""
# 查看数据库的创建日期和归档方式
sql["log_mode_log_create_time"] = """SELECT created, log_mode, log_mode FROM v$database"""